﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient ;
//using System.Data.SqlClient.SqlTransaction;
using Microsoft.SqlServer.Server;

namespace DAO.KetNoi
{
    public class clsConnect
    {
        public static SqlConnection open()
        {
            //private string connString = "Data Source=.\\SQLEXPRESS;AttachDbFilename=|DataDirectory|\\QLHocSinh.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True";

            //Data Source=PHAMVANVINH-PC;Initial Catalog=QLHocSinh;Persist Security Info=True;User ID=sa;Password=123
            SqlConnection connection = new SqlConnection("Data Source=PHAMVANVINH-PC;Initial Catalog=QLHocSinh;Persist Security Info=True;User ID=sa;Password=123");
                connection.Open();
                return connection;   
        }
        public static void close(SqlConnection connection)
        {
            if (connection != null && connection.State == ConnectionState.Open)
            {
                connection.Close();
            }
        }
        /// <summary>
        /// Ham thuc hien Insert,Update,Delete
        /// </summary>
        /// <param name="sql">Cau SQL truyen vao</param>
        /// <returns></returns>
        public static int executeNonQuery(string sql)
        {

            using (SqlConnection conn = open())
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                int x = -1;
                x = cmd.ExecuteNonQuery();
                close(conn);
                return x;
            }
                    
        }
        /// <summary>
        /// Ham thuc hien Insert,Update,Delete
        /// </summary>
        /// <param name="sql">Cau SQL truyen vao</param>
        /// <returns></returns>
        public static bool executeNonQuery(object [] mang)
        {
            //SqlTransaction sTran;
            //using (SqlConnection conn = open())
            //    try
            //    {
            //        sTran = new SqlTransaction();
            //        conn.BeginTransaction();
            //        for (int i = 0; i < mang.Length - 1; i++)
            //        {
            //            String chuoi = mang[i].ToString ();
            //            SqlCommand cmd = new SqlCommand(chuoi, conn);
            //            cmd.ExecuteNonQuery();
            //            close(conn);
            //        }
            //        sTran.Commit();
            //        return true;
            //    }
            //    catch (Exception ex)
            //    {
            //        sTran.Rollback();
            //    }
            return false;
        }
        //public int executeNonQuery(string sql)
        //{
        //    SqlConnection conn = open();
        //    SqlCommand cmd = new SqlCommand(sql, conn);
        //    int x = -1;
        //    x = cmd.ExecuteNonQuery();
        //    close(conn);
        //    return x;

        //}
        /// <summary>
        /// Ham thuc hien cau lenh select du lieu do vao mot bang
        /// </summary>
        /// <param name="sql">Câu select truyển vào</param>
        /// <returns></returns>
        public static DataTable executeNonQueryTable(string sql)
        {
            DataTable dt = new DataTable();
            SqlConnection conn = open();
            try
            {
                SqlCommand cmd = new SqlCommand(sql, conn);
                SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
                adp.Fill(dt);
                close(conn);
            }
            finally
            {
                if (conn != null)
                    conn.Dispose();
            }
            return dt;
        }
        //public static DataTable executeNonQueryTable(string sql)
        //{
        //    DataTable dt = new DataTable();
        //    SqlConnection conn = open();
        //    SqlCommand cmd = new SqlCommand(sql, conn);
        //    SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
        //    adp.Fill(dt);
        //    close(conn);
        //    return dt;
        //}
        /// <summary>
        /// Ham thuc hien cau lenh select du lieu do vao mot dataset
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static DataSet executeQuerydataset(string sql)
        {
            SqlCommand cmd = new SqlCommand(sql, open());
            SqlDataAdapter adp = new SqlDataAdapter(sql, open());
            DataSet ds = new DataSet();
            adp.Fill(ds);
            return ds;
        }
        //public static DataSet executeQuerydataset(string sql)
        //{
        //    SqlConnection conn = open();
        //    SqlCommand cmd = new SqlCommand(sql, conn);
        //    SqlDataAdapter adp = new SqlDataAdapter(sql, conn);
        //    DataSet ds = new DataSet();
        //    adp.Fill(ds);
        //    return ds;
        //}
    }
}
